/**
 * 
 */
package org.linkminder.persist;

import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;

import java.util.logging.Logger;
import org.linkminder.LinkMinderException;
import org.linkminder.api.ILinkMinderDataProvider;
import org.linkminder.api.ILinkMinderSchema;

/**
 * @author jackpark
 *
 */
public class LinkMinderDatabase implements ILinkMinderDataProvider {
	private Logger log = Logger.getLogger(LinkMinderDatabase.class.getName());
	  public static final String derbyJdbcDriver =
	      "org.apache.derby.jdbc.EmbeddedDriver";
	  private String userName, password, connectionString;
	  private String databaseName;

	/**
	 * @param dbname
	 * @param username
	 * @param pwd
	 * @param filePath
	 * @throws LinkMinderException
	 */
	public LinkMinderDatabase(String dbname, String username, String pwd, String filePath) 
			throws LinkMinderException {
		userName = username;
		databaseName = dbname;
		password = pwd;
		connectionString = "jdbc:derby:"+filePath+"/"+dbname+";create=true;";
		try {
			Class.forName(derbyJdbcDriver).newInstance();
		} catch (Exception e) {
			log.info("LinkMinderDatabase error "+e.getMessage());
			throw new LinkMinderException(e);
		}
		validate();
	    log.info("LinkMinderDatabase started: "+connectionString);

	}
	
	public Connection getConnection() throws LinkMinderException {
		try {
			return DriverManager.getConnection(connectionString);
		} catch (SQLException e) {
			log.info("LinkMinderDatabase.getConnection error "+e.getMessage());
			throw new LinkMinderException(e);
		}
	}

	/* (non-Javadoc)
	 * @see org.linkminder.api.ILinkMinderDataProvider#associate(java.lang.String, java.lang.String)
	 */
	@Override
	public List<String> associate(Connection con, String idea, String associatedIdea)
			throws LinkMinderException {
		PreparedStatement s = null;
		try {
			s = con.prepareStatement(ILinkMinderSchema.insertAssociatedIdea);
			s.setString(1, idea);
			s.setString(2, associatedIdea);
			s.execute();
		} catch (SQLException e) {
			log.info("LinkMinderDatabase.associate error "+e.getMessage());
			throw new LinkMinderException(e);
		} finally {
			close(s);
		}
		return get(con,idea);
	}

	/* (non-Javadoc)
	 * @see org.linkminder.api.ILinkMinderDataProvider#get(java.lang.String)
	 */
	@Override
	public List<String> get(Connection con, String idea) throws LinkMinderException {
		List<String> result = new ArrayList<String>();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			s = con.prepareStatement(ILinkMinderSchema.insertAssociatedIdea);
			s.setString(1, idea);
			rs = s.executeQuery();
			while(rs.next()) {
				result.add(rs.getString("associatedIdea"));
			}
		} catch (SQLException e) {
			log.info("LinkMinderDatabase.get error "+e.getMessage());
			throw new LinkMinderException(e);
		} finally {
			close(s);
			close(rs);
		}
		return result;
	}

	/**
	 * See to it that the database has been initialized
	 * @throws LinkMinderException
	 */
	void validate() throws LinkMinderException {
		Connection con = null;
		try {
			log.info("LinkMinderDatabase.validate-");
			con = getConnection();
			log.info("LinkMinderDatabase.validate 1 "+con);
			List<String> px = this.get(null, "foo");
			log.info("LinkMinderDatabase.validate 2 "+px);
			//will return an empty list unless there just happens to be an idea "foo"
			//will toss an exception if the database is not initialized
		} catch (Exception e1) {
			log.info("LinkMinderDatabase.validate 3");
		    try {
			      String[] sql = ILinkMinderSchema.IDEA_SCHEMA;
			      int len = sql.length;
			      con = getConnection();
			      Statement s = con.createStatement();
			      for (int i = 0; i < len; i++) {
			    	log.info(sql[i]);
			        s.execute(sql[i]);
			      }
			      s.close();
			      con.close();
			    }
			    catch (SQLException e) {
			    	log.info("LinkMinderDatabase.validate error "+e.getMessage());
			    	throw new LinkMinderException(e);
			    }
		} 
	}
	
	public void shutDown() {
		  try {
	              DriverManager.getConnection("jdbc:derby:"+databaseName+";shutdown=true");
		  } catch (SQLException e) {
			  log.info("DerbyDatabase.shutDownDerby "+databaseName+" error "+e.getMessage());
		  }
	}
	
	void close(ResultSet s) throws LinkMinderException {
		try {
	          if(s != null) s.close();
	      } catch(SQLException e) {
	        log.info("UserDatabase.close ResultSet error "+e.getMessage());
	        throw new LinkMinderException(e);
	      }
	}
	void close(PreparedStatement s) throws LinkMinderException {
	    try {
	          if(s != null) s.close();
	      } catch(SQLException e) {
	        log.info("UserDatabase.close PreparedStatement error "+e.getMessage());
	        throw new LinkMinderException(e);
	      }
	}


}
